﻿using BW.WMS.Application.Features.Product.Dtos;
using BW.WMS.Application.Features.Product.Interfces;
using BW.WMS.Domain.Entities.Product;
using BW.WMS.Infrastructure.Data.Repositories;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using BW.WMS.Infrastructure.Data.Contexts;
using BW.WMS.Core.Result;
using System.Diagnostics.CodeAnalysis;
using Newtonsoft.Json;
using Yitter.IdGenerator;
using BW.WMS.Infrastructure.Services;
using System.Security.Cryptography;

namespace BW.WMS.Application.Features.Product.Services
{
    public class ProductService : IProductService
    {
   
        private readonly DBContext dbContext;
        private readonly IRedisCacheService redisCacheService;
        private const int CacheExpirationMinutes = 30; // 缓存过期时间30分钟

        public ProductService(DBContext dbContext, IRedisCacheService redisCacheService)
        {
            this.dbContext = dbContext;
            this.redisCacheService = redisCacheService;
        }

        public async Task<ApiResult<int>> CreateProduct(ProductInputDto input)
        {
            var result = new ApiResult<int>();
            try
            {
                //获取数据库链接
                var connection = dbContext.GetConn();
                //SQL语句
                var checkSql = $@"select count(1) from Product
                                 Where ProductName = @ProductName 
                                 And ProductTypeId = @ProductTypeId
                                 And IsDeleted = 0";
                //Dapper参数对象，安全传递参数
                var parameters = new DynamicParameters();
                parameters.Add("@ProductName",input.ProductName);
                parameters.Add("@ProductTypeId", input.ProductTypeId);

                //异步查询，返回单个值
                var existsCount = await connection.ExecuteScalarAsync<int>(checkSql,parameters);

                if (existsCount > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "产品已存在";
                    result.Result = 0;
                    return result;
                }


                string auxiliaryList = null;
                if (input.AuxiliaryUnitList?.Any() == true)
                {
                    auxiliaryList = JsonConvert.SerializeObject(input.AuxiliaryUnitList);
                }

                long newId = 0;
                long unitId = 0;
                long product_unitId = 0;

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var insertUnitSql = @"
                        INSERT INTO Unit (
                        Id,UnitTypeId,BarCode,AuxiliaryUnitList
                        ) VALUES(
                        @Id,@UnitTypeId,@BarCode,@AuxiliaryUnitList
                        );
                        SELECT LAST_INSERT_ID();
                    ";
                        var insertUnitParameters = new DynamicParameters();
                        insertUnitParameters.Add("@Id", YitIdHelper.NextId());
                        insertUnitParameters.Add("@UnitTypeId", input.UnitTypeId);
                        insertUnitParameters.Add("@BarCode", input.BarCode);
                        insertUnitParameters.Add("@AuxiliaryUnitList", input.AuxiliaryUnitList);

                        unitId = await connection.ExecuteScalarAsync<long>(insertUnitSql, insertUnitParameters);

                        //新增语句
                        var insertSql = @"INSERT INTO Product (
                        Id,ProductCode,ProductName,ProductTypeId,Specification,Unit,BarCode,SupplierId,ClientNameId,WarehousingPrice,OutboundPrice,Number,Price,ExpirationDateStatus,ExpirationDate,WarningDate,CreateBy,CreateTime,IsDeleted,Remark
                        ) VALUES (
                        @Id,@ProductCode,@ProductName,@ProductTypeId,@Specification,@Unit,@BarCode,@SupplierId,@ClientNameId,@WarehousingPrice,@OutboundPrice,@Number,@Price,@ExpirationDateStatus,@ExpirationDate,@WarningDate,@CreateBy,@CreateTime,@IsDeleted,@Remark
                        );
                        Select LAST_INSERT_ID();";

                        //Dapper参数对象，安全传递参数
                        var insertParameters = new DynamicParameters();
                        insertParameters.Add("@ProductCode", input.ProductCode);
                        insertParameters.Add("@ProductName", input.ProductName);
                        insertParameters.Add("@ProductTypeId", input.ProductTypeId);
                        insertParameters.Add("@Specification", input.Specification);
                        insertParameters.Add("@Unit", input.Unit);
                        insertParameters.Add("@BarCode", input.BarCode);
                        insertParameters.Add("@SupplierId", input.SupplierId);
                        insertParameters.Add("@ClientNameId", input.ClientNameId);
                        insertParameters.Add("@WarehousingPrice", input.WarehousingPrice);
                        insertParameters.Add("@OutboundPrice", input.OutboundPrice);
                        insertParameters.Add("@Number", input.Number);
                        insertParameters.Add("@Price", input.Price);
                        insertParameters.Add("@ExpirationDateStatus", input.ExpirationDateStatus);
                        insertParameters.Add("@ExpirationDate", input.ExpirationDate);
                        insertParameters.Add("@WarningDate", input.WarningDate);
                        insertParameters.Add("@CreateBy", "admin");
                        insertParameters.Add("@CreateTime", DateTime.Now);

                        //返回单个值
                        newId = await connection.ExecuteScalarAsync<long>(insertSql, insertParameters);

                        var insertProduct_UnitSql = @"INSERT INTO Product_UnitMiddle (Id,ProductId,UnitId,CreateBy,CreateTime) VALUES (@Id,@ProductId,@UnitID,@CreateBy,@CreateTime);
                      Select LAST_INSERT_ID();";

                        var product_UnitParameters = new DynamicParameters();
                        product_UnitParameters.Add("@Id", YitIdHelper.NextId());
                        product_UnitParameters.Add("@ProductId", newId);
                        product_UnitParameters.Add("@UnitID", unitId);
                        product_UnitParameters.Add("@CreateBy", DateTime.Now);

                        product_unitId = await connection.ExecuteScalarAsync<long>(insertProduct_UnitSql, product_UnitParameters);

                        transaction.Commit();
                    }
                    catch (Exception)
                    {

                        transaction.Rollback();
                    }
                    
                }







                if (newId > 0 && product_unitId > 0 && unitId > 0)
                {
                    result.Code = ApiEnums.Success;
                    result.Message = "货品信息添加成功:" + newId;
                    result.Result = 1;
                    
                    // 清理产品列表缓存 (异步执行，不影响主流程)
                    _ = Task.Run(async () => await ClearProductListCacheAsync());
                }
                else
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "货品信息添加失败";
                    result.Result = 0;
                }
                
            }
            catch (Exception)
            {

                result.Code = ApiEnums.Error;
                result.Message = "货品信息添加异常";
                result.Result = 0;
            }

            return result;
        }

        public Task<ApiResult<int>> DeletedProduct(long id)
        {
            throw new NotImplementedException();
        }

        public Task<ApiResult<ProductUpdateInputDto>> FindnProduct(long id)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 货品信息分页查询 (支持Redis集群缓存)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<ApiPage<ProductWithTypeDto>> GetProductList(InputProductDtos input)
        {
            // 创建返回结果
            var apiPage = new ApiPage<ProductWithTypeDto>
            {
                Code = ApiEnums.Success,
                Message = "查询成功",
                PageData = new List<ProductWithTypeDto>()
            };

            try
            {
                // 生成缓存键
                var cacheKey = GenerateProductListCacheKey(input);
                
                // 尝试从Redis缓存获取数据
                var cachedResult = await redisCacheService.GetAsync<ApiPage<ProductWithTypeDto>>(cacheKey);
                if (cachedResult != null)
                {
                    // 命中缓存，直接返回
                    cachedResult.Message += " (来自缓存)";
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 计算分页偏移量
                int offset = (input.PageIndex - 1) * input.PageSize;
                
                // 构建查询条件
                StringBuilder whereClause = new StringBuilder();
                var parameters = new DynamicParameters();
                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", input.PageSize);
                
                // 添加查询条件
                if (!string.IsNullOrEmpty(input.ProductName))
                {
                    whereClause.Append(" AND p.ProductName LIKE @ProductName ");
                    parameters.Add("@ProductName", $"%{input.ProductName}%");
                }
                
                if (!string.IsNullOrEmpty(input.Unit))
                {
                    whereClause.Append(" AND p.Unit LIKE @Unit ");
                    parameters.Add("@Unit", $"%{input.Unit}%");
                }
                
                if (!string.IsNullOrEmpty(input.BarCode))
                {
                    whereClause.Append(" AND p.BarCode LIKE @BarCode ");
                    parameters.Add("@BarCode", $"%{input.BarCode}%");
                }
                
                if (!string.IsNullOrEmpty(input.ProductTypeName))
                {
                    whereClause.Append(" AND pt.ProductTypeName LIKE @ProductTypeName ");
                    parameters.Add("@ProductTypeName", $"%{input.ProductTypeName}%");
                }
                
                // 构建完整SQL语句
                string sql = $@"SELECT 
                            p.Id ,
                            p.ProductCode ,
                            p.ProductName ,
                            p.Specification ,
                            p.Unit ,
                            p.BarCode ,
                            p.SupplierId,
                            p.WarehousingPrice,
                            p.OutboundPrice ,
                            p.Number ,
                            p.Price ,
                            p.CreateBy ,
                            p.CreateTime ,
                            pt.ProductTypeCode ,
                            pt.ProductTypeName ,
                            pt.Remark 
                        FROM Product p
                        INNER JOIN ProductType pt ON p.ProductTypeId = pt.Id
                        WHERE 1=1 {whereClause}
                        ORDER BY p.CreateTime DESC
                        LIMIT @Offset, @PageSize;";
                        
                // 查询总记录数
                string countSql = $@"SELECT COUNT(1)
                        FROM Product p
                        INNER JOIN ProductType pt ON p.ProductTypeId = pt.Id
                        WHERE 1=1 {whereClause}";
                        
                // 执行查询
                var connection = dbContext.GetConn();
                var result = await connection.QueryAsync<ProductWithTypeDto>(sql, parameters);
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);
                
                // 设置返回数据
                apiPage.PageData = result.ToList();
                apiPage.TodalCount = totalCount;

                // 设置分页信息
               // apiPage.PageCount = (int)Math.Ceiling(totalCount / (double)input.PageSize);

                // 将查询结果缓存到Redis (设置过期时间)
                var cacheExpiry = TimeSpan.FromMinutes(CacheExpirationMinutes);
                await redisCacheService.SetAsync(cacheKey, apiPage, cacheExpiry);
            }
            catch (Exception ex)
            {
                // 发生异常时设置错误信息
                apiPage.Code = ApiEnums.Error;
                apiPage.Message = $"查询失败：{ex.Message}";
            }
            
            return apiPage;
        }

        public Task<ApiResult<int>> UpdateProduct(ProductUpdateInputDto input)
        {
            throw new NotImplementedException();
        }

        #region Redis缓存相关方法

        /// <summary>
        /// 生成货品列表缓存键
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>缓存键</returns>
        private string GenerateProductListCacheKey(InputProductDtos input)
        {
            // 构建缓存键的基础部分
            var keyBuilder = new StringBuilder("ProductList");
            
            // 添加分页参数
            keyBuilder.Append($":Page{input.PageIndex}:Size{input.PageSize}");
            
            // 添加查询条件参数 (只有非空的才加入)
            if (!string.IsNullOrEmpty(input.ProductName))
            {
                keyBuilder.Append($":Name{GetSafeKeyValue(input.ProductName)}");
            }
            
            if (!string.IsNullOrEmpty(input.Unit))
            {
                keyBuilder.Append($":Unit{GetSafeKeyValue(input.Unit)}");
            }
            
            if (!string.IsNullOrEmpty(input.BarCode))
            {
                keyBuilder.Append($":BarCode{GetSafeKeyValue(input.BarCode)}");
            }
            
            if (!string.IsNullOrEmpty(input.ProductTypeName))
            {
                keyBuilder.Append($":TypeName{GetSafeKeyValue(input.ProductTypeName)}");
            }
            
            // 对整个键进行MD5哈希，防止键过长
            return "WMS:" + GenerateMD5Hash(keyBuilder.ToString());
        }

        /// <summary>
        /// 清理产品相关缓存 (当产品数据发生变更时调用)
        /// </summary>
        /// <returns></returns>
        public async Task<bool> ClearProductListCacheAsync()
        {
            try
            {
                // 使用通配符模式删除所有产品列表缓存
                // 注意：这需要Redis支持SCAN命令，在集群环境下需要特别处理
                var keys = new List<string>();
                
                // 生成一些常用的缓存键模式进行删除
                // 实际项目中建议维护一个缓存键的集合或使用Redis的标签功能
                for (int pageIndex = 1; pageIndex <= 50; pageIndex++) // 假设最多50页
                {
                    for (int pageSize = 10; pageSize <= 100; pageSize += 10) // 常用的页面大小
                    {
                        var emptyInput = new InputProductDtos { PageIndex = pageIndex, PageSize = pageSize };
                        keys.Add(GenerateProductListCacheKey(emptyInput));
                    }
                }
                
                if (keys.Any())
                {
                    await redisCacheService.DeleteAsync(keys);
                }
                
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 生成MD5哈希值
        /// </summary>
        /// <param name="input">输入字符串</param>
        /// <returns>MD5哈希值</returns>
        private string GenerateMD5Hash(string input)
        {
            using (var md5 = MD5.Create())
            {
                var bytes = Encoding.UTF8.GetBytes(input);
                var hashBytes = md5.ComputeHash(bytes);
                return Convert.ToHexString(hashBytes).ToLower();
            }
        }

        /// <summary>
        /// 获取安全的缓存键值 (移除特殊字符)
        /// </summary>
        /// <param name="value">原始值</param>
        /// <returns>安全的键值</returns>
        private string GetSafeKeyValue(string value)
        {
            if (string.IsNullOrEmpty(value))
                return string.Empty;
                
            // 移除或替换特殊字符，确保Redis键的安全性
            return value.Replace(":", "_")
                       .Replace(" ", "_")
                       .Replace("*", "_")
                       .Replace("?", "_")
                       .Replace("[", "_")
                       .Replace("]", "_");
        }

        #endregion
    }
}
